-（1）假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，
--则自动添加部门信息，部门名称为"新部门"。
create trigger tri_AddPeople
on People  for insert
as
declare @DepartmentId int
select @DepartmentId = DepartmentId from People
if not exists(select * from Department where DepartmentId = @DepartmentId)
	insert into Department values(@DepartmentId,'新部门')

insert into People values('007','呼呼','男',15000)
go
--（2）触发器实现，删除一个部门的时候将部门下所有员工全部删除。
create trigger tri_Delete
on Department for delete 
as
delete People where DepartmentId = (select DepartmentId from deleted)
go
delete Department where DepartmentId = 1
go

--（4）修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改
create trigger tri_UpdateDepartment
on Department for update
as
declare @Old_DepartmentId int ,@New_DepartmentId int
select @Old_DepartmentId = (select DepartmentId from deleted)
select @New_DepartmentId = (select DepartmentId from inserted)
update People set DepartmentId = @New_DepartmentId where DepartmentId = @Old_DepartmentId

update Department set DepartmentId = '003' where DepartmentId = '001'

--总经办-1000  市场部员工工资-2000
--用存储过程和游标
declare cur_PayCut cursor scroll
for select DepartmentId from Department

go
create proc proc_PayCut
as
begin
	open cur_PayCut
	declare @DepartmentId int,@money money = 1000
	fetch first from cur_PayCut into @DepartmentId
	while(@@FETCH_STATUS = 0)
	begin
		update People set PeopleSalary -= @money where DepartmentId = @DepartmentId
		set @money += 1000
		fetch next from cur_PayCut into @DepartmentId
	end
	close cur_PayCut
end

exec proc_PayCut